# Alex Gazmararian
# agazmararian@gmail.com

library(tidyverse)
library(tidylog)
library(readxl)
library(here)
library(modelsummary)

bb <- read.csv(here("data", "input", "broadband", "county_tiers_201406_202406.csv"))
bb <- filter(bb, Month == 6) # Mid year to include 2024
bb <- bb %>%
  rename(bb100 = Tier_4) %>%
  mutate(
    bb100 = case_when(
      bb100 == 0 ~ "0",
      bb100 == 1 ~ "0-200",
      bb100 == 2 ~ "200-400",
      bb100 == 3 ~ "400-600",
      bb100 == 4 ~ "600-800",
      bb100 == 5 ~ ">800",
      bb100 == -999 ~ NA_character_
    )
  )

bb <- subset(bb, select = c(FIPS, bb100, Year))
names(bb) <- c("fips", "bb100", "year")

bb$bb100_bin <- case_when(
  is.na(bb$bb100) ~ NA_integer_,
  bb$bb100 %in% c("0-200", "200-400", "400-600") ~ 0L,
  TRUE ~ 1L
)

bb <- bb %>%
  mutate(
    bb100 = case_when(
      bb100 %in% c("0-200", "200-400") ~ "0-400",
      T ~ as.character(bb100)
    )
  )

bb$bb100 <- relevel(factor(bb$bb100), ref = "0-400")

# Harmonize FIPS
bb$fips.post <- bb$fips
bb$fips[bb$fips == 09120] <- 09001

# Lag one year
bb$year_lag <- bb$year + 1

write_csv(bb, here("data", "inter", "broadband_county_processed.csv"))
message("Saved broadband data")